package model.dao.registerOrder;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import log.MyLogger;
import model.service.Employee;
import model.service.ServiceOrder;
import model.service.SoStatusType;



public class ServiceOrderDAOImpl{
	String sql = "" ;
	db.dbconnection dbconnection ;
	Connection myConnection ;
	Statement st ;


	public List<ServiceOrder> getPersonServiceOrders(int subjectFk) {
		List<ServiceOrder> serviceOrders = new ArrayList<ServiceOrder>();
		try {
			dbconnection = new db.dbconnection();
			myConnection= dbconnection.getConnection();
			Statement st = myConnection.createStatement();
			String sql = "SELECT * from f_get_person_service_order("+subjectFk+")";
			//MyLogger.LogMessage(sql);
			st.executeQuery(sql);
			ResultSet r = st.getResultSet();
//			PersonDAOImpl personDAOImpl = new PersonDAOImpl();
//			personDAOImpl.setDbConnection(dbconnection);
			EmployeeDAOImpl employeeDAOImpl = new EmployeeDAOImpl();
			ServicePartDAOImpl servicePartDAOImpl = new ServicePartDAOImpl();
			ServiceDeviceDAOImpl serviceDeviceDAOImpl = new ServiceDeviceDAOImpl();
			ServiceActionDAOImpl serviceActionDAOImpl = new ServiceActionDAOImpl();
			ServiceRequestDAOImpl serviceRequestDAOImpl = new ServiceRequestDAOImpl();

			while(r.next()){
				ServiceOrder so = new ServiceOrder();
				so.setServiceOrder(r.getInt("service_order"));
				//so.setStatus(r.getString("type_name"));
				so.setSoStatusType(new SoStatusType(r.getString("type_name")));
				//so.setCreatedBy(personDAOImpl.getPersonById(r.getInt("created_by")));
				//MyLogger.LogMessage(r.getString("type_name"));
				
				so.setCreatedBy(new Employee(r.getInt("created_by"), employeeDAOImpl.getPersonByEmployeeId(r.getInt("created_by"))));
				so.setCreated(r.getTimestamp("created"));
				so.setPriceTotal(r.getInt("price_total"));
				so.setNote(r.getString("note"));
				so.setServiceDevices(serviceDeviceDAOImpl.getServiceDevicesByServiceOrder(so.getServiceOrder()));
				so.setServiceParts(servicePartDAOImpl.getServiceParts(so.getServiceOrder()));
				so.setServiceActions(serviceActionDAOImpl.getServiceActions(so.getServiceOrder()));
				so.setServiceRequest(serviceRequestDAOImpl.getServiceRequestById(r.getInt("service_request_fk")));
				serviceOrders.add(so);
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.getPersonServiceOrders()", e.getMessage());
		}



		return serviceOrders;
	}

	public List<ServiceOrder> getEnterpriseServiceOrders(int subjectFk) {
		List<ServiceOrder> serviceOrders = new ArrayList<ServiceOrder>();
		try {
			myConnection = dbconnection.getConnection();
			Statement st = myConnection.createStatement();
			String sql = "SELECT * from f_get_enterprise_service_order("+subjectFk+")";

			st.executeQuery(sql);
			ResultSet r = st.getResultSet();
//			PersonDAOImpl personDAOImpl = new PersonDAOImpl();
//			personDAOImpl.setDbConnection(dbconnection);
			EmployeeDAOImpl employeeDAOImpl = new EmployeeDAOImpl();
			ServiceRequestDAOImpl serviceRequestDAOImpl = new ServiceRequestDAOImpl();
			
			while(r.next()){
				ServiceOrder so = new ServiceOrder();
				so.setServiceOrder(r.getInt("service_order"));
				//so.setStatus(r.getString("type_name"));
				so.setSoStatusType(new SoStatusType(r.getString("type_name")));
				//so.setCreatedBy(personDAOImpl.getPersonById(r.getInt("created_by")));
				so.setCreatedBy(new Employee(r.getInt("created_by"), employeeDAOImpl.getPersonByEmployeeId(r.getInt("created_by"))));
				so.setCreated(r.getTimestamp("created"));
				so.setPriceTotal(r.getInt("price_total"));
				so.setNote(r.getString("note"));
				so.setServiceRequest(serviceRequestDAOImpl.getServiceRequestById(r.getInt("service_request_fk")));
				serviceOrders.add(so);
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.getEnterpriseServiceOrders()", e.getMessage());
		}
		return serviceOrders;
	}

	/**
	 * @param serviceOrder
	 * @return inserted service order id
	 */
	public int insertServiceOrder(ServiceOrder serviceOrder) {
		int serviceOrderId = 0;
		try {
			Statement st = dbconnection.getConnection().createStatement();
			String sql = "select f_insert_service_order AS id from f_insert_service_order("+serviceOrder.getCreatedBy().getEmployee()+","+serviceOrder.getServiceRequest().getRequest()+",'"+serviceOrder.getNote()+"')";
			ResultSet rs = st.executeQuery(sql);

			while(rs.next()){
				serviceOrderId = rs.getInt("id");
			}
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.insertServiceOrder()", e.getMessage());
		}
		
		

		
		
		
		return serviceOrderId;
	}

	public String getServiceOrderStatusType(int serviceOrder) {
		String type_name = new String();
		try {
			Statement st = dbconnection.getConnection().createStatement();
			String sql = "SELECT type_name FROM service_order LEFT JOIN so_status_type ON service_order.so_status_type_fk = so_status_type.so_status_type WHERE service_order = "+ serviceOrder;
			ResultSet r = st.executeQuery(sql);
			
			while(r.next()){
				type_name = r.getString("type_name");
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.getServiceOrder()", e.getMessage());
		}
		return type_name;
	}

	public ServiceOrder getServiceOrderById(int serviceOrderId) {
		if(serviceOrderId < 1){
			return null;
		}
		
		boolean ok = false;
		ServiceOrder serviceOrder = new ServiceOrder();
		serviceOrder.setServiceOrder(serviceOrderId);
		try {
			Statement st = dbconnection.getConnection().createStatement();
			String sql = "SELECT * FROM service_order WHERE service_order = "+ serviceOrderId;
			ResultSet r = st.executeQuery(sql);
			
			
			
			EmployeeDAOImpl employeeDAOImpl = new EmployeeDAOImpl();
			ServiceRequestDAOImpl serviceRequestDAOImpl = new ServiceRequestDAOImpl();
			ServiceActionDAOImpl serviceActionDAOImpl = new ServiceActionDAOImpl();
			ServicePartDAOImpl servicePartDAOImpl = new ServicePartDAOImpl();
			ServiceDeviceDAOImpl serviceDeviceDAOImpl = new ServiceDeviceDAOImpl();
			SoStatusTypeDAOImpl soStatusTypeDAOImpl = new SoStatusTypeDAOImpl();
			
			
			while(r.next()){
				serviceOrder.setCreated(r.getTimestamp("created"));
				serviceOrder.setCreatedBy(new Employee(r.getInt("created_by"), employeeDAOImpl.getPersonByEmployeeId(r.getInt("created_by"))));
				serviceOrder.setNote(r.getString("note"));
				serviceOrder.setSoStatusType(soStatusTypeDAOImpl.getStatusTypeById(r.getInt("so_status_type_fk")));
				serviceOrder.setServiceRequest(serviceRequestDAOImpl.getServiceRequestById(r.getInt("service_request_fk")));
				serviceOrder.setUpdatedBy(new Employee(r.getInt("updated_by"), employeeDAOImpl.getPersonByEmployeeId(r.getInt("updated_by"))));
				serviceOrder.setUpdated(r.getTimestamp("updated"));
				serviceOrder.setStatusChanged(r.getTimestamp("status_changed"));
				serviceOrder.setStatusChangedBy(new Employee(r.getInt("status_changed_by"), employeeDAOImpl.getPersonByEmployeeId(r.getInt("status_changed_by"))));
				serviceOrder.setPriceTotal(r.getFloat("price_total"));
				serviceOrder.setServiceActions(serviceActionDAOImpl.getServiceActions(serviceOrderId));
				serviceOrder.setServiceParts(servicePartDAOImpl.getServiceParts(serviceOrderId));
				serviceOrder.setServiceDevices(serviceDeviceDAOImpl.getServiceDevicesByServiceOrder(serviceOrderId));
				ok = true;	
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.getServiceOrderById()", e.getMessage());
			ok = false;
		}
		
		if(ok){
			return serviceOrder;
		}
		else{
			return null;
		}
	}

	public ServiceOrder getServiceOrderByServiceRequestId(int service_request_id) {
		try {
			Statement st = dbconnection.getConnection().createStatement();
			String sql = "SELECT service_order FROM service_order WHERE service_request_fk = "+ service_request_id;
			ResultSet r = st.executeQuery(sql);
			int service_order = 0;
			while(r.next()){
				service_order = r.getInt("service_order");
			}
			this.dbconnection.close();
			if(service_order > 0){
				return getServiceOrderById(service_order);
			}
			else{
				return null;
			}
			
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.getServiceOrderByServiceRequestId()", e.getMessage());
			return null;
		}
	}

	public boolean updateServiceOrder(ServiceOrder so) {
		try {
			Statement st = dbconnection.getConnection().createStatement();
			//poolik vajab täiendamist kui vaja
			String sql = "UPDATE service_order SET so_status_type_fk="+so.getSoStatusType().getSoStatusType()+" WHERE service_order="+so.getServiceOrder();
			st.executeUpdate(sql);
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.updateServiceOrder()", e.getMessage());
			return false;
		}
		return true;
	}

	public boolean deleteServiceOrder(int service_order_id) {
		try {
			Statement st = dbconnection.getConnection().createStatement();
			//poolik vajab täiendamist kui vaja
			String sql = "DELETE FROM service_order WHERE service_order="+service_order_id;
			st.executeUpdate(sql);
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("ServiceOrderDAOImpl.updateServiceOrder()", e.getMessage());
			return false;
		}
		return true;
	}



}
